package database.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import database.pojo.PlayerCollectSingerInfo;
import database.pojo.UserInfo;

public class PlayerCollectSingerInfoDao{
	public boolean insert(PlayerCollectSingerInfo pInfo, Connection conn ) throws SQLException {
		String sqlInsert = "insert into PlayerCollectSinger(openId, singerId, singerTimes) values( ? , ? , ? )";
		PreparedStatement pstmt = null;
		boolean result = true;
		try{
			pstmt = conn.prepareStatement(sqlInsert);//相当于python操作数据库的游标
			pstmt.setString(1, pInfo.getOpenId());
			pstmt.setInt(2, pInfo.getSingerId());
			//System.out.println("insert---getSingTimes"+pInfo.getSingTimes());
			pstmt.setInt(3, pInfo.getSingTimes());
			pstmt.executeUpdate();
		}catch (Exception e) {
			System.out.println(" insert Exception" + e);
			result = true;
		}
		finally{
			if(pstmt != null){
				pstmt.close();
			}
		}
		return result;
		
	}
	
	public boolean delete(String openId,int singerId, Connection conn ) throws SQLException {
		String sqlDelete = "delete  from PlayerCollectSinger where openId = ? and singerId = ?";
		PreparedStatement pstmt = null;
		boolean result = true;
		try{
			pstmt = conn.prepareStatement(sqlDelete);//相当于python操作数据库的游标
			pstmt.setString(1, openId);
			pstmt.setInt(2, singerId);
			//System.out.println("delect---getsingerId"+singerId);
			pstmt.executeUpdate();
		}catch (Exception e) {
			System.out.println(" delect Exception" + e);
			result = true;
		}
		finally{
			if(pstmt != null){
				pstmt.close();
			}
		}
		return result;
		
	}
	
	public PlayerCollectSingerInfo update(PlayerCollectSingerInfo pInfo , Connection conn) throws SQLException {
		String sqlUpdate = "update PlayerCollectSinger set singerTimes = ? where openId = ? and singerId = ?";
		PreparedStatement pstmt = null;
		
		try {
			pstmt = conn.prepareStatement(sqlUpdate);
			pstmt.setInt(1, pInfo.getSingTimes());
			pstmt.setString(2, pInfo.getOpenId());
			pstmt.setInt(3, pInfo.getSingerId());
			pstmt.executeUpdate();
			
		}catch (Exception e) {
			System.out.println(" update Exception" + e);
		} 
		finally {
			if(pstmt != null) {
				pstmt.close();
			}
		}
		return pInfo;
		
	}
	
	public ArrayList<PlayerCollectSingerInfo> selectAllByOpenId(String openId,Connection conn) throws SQLException {
		
		String sqlSelect = "select * from PlayerCollectSinger where openId = ? ";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<PlayerCollectSingerInfo> pinfoList = new ArrayList<PlayerCollectSingerInfo>();
		PlayerCollectSingerInfo pInfo = null;
		//System.out.println("get open id " + openId);
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setString(1, openId);
			rs = pstmt.executeQuery();
			while(rs.next()){
				//System.out.println("selectAllByopenId-------singerId--"+rs.getString("singerId"));
				pInfo =new PlayerCollectSingerInfo();
				pInfo.setOpenId(rs.getString("openId"));
				pInfo.setSingerId(rs.getInt("singerId"));
				pInfo.setSingTimes(rs.getInt("singerTimes"));
				pinfoList.add(pInfo);
				}
			}finally {
				if(pstmt != null){
					pstmt.close();
				}
			}
		
		return pinfoList;
	}
	
	public boolean isExist(PlayerCollectSingerInfo pInfo, Connection conn) throws SQLException{
		String sqlSelect = "select * from PlayerCollectSinger where openId = ? and singerId = ?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = conn.prepareStatement(sqlSelect);
			pstmt.setString(1, pInfo.getOpenId());
			pstmt.setInt(2, pInfo.getSingerId());
			rs = pstmt.executeQuery();
			if(rs.next())

				if(rs.getString("openId") != null)
					return true;
				else {
					return false;
				}
			else {
				 return false;
			}
		}
		finally{
			if(rs != null){
				rs.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
		}
	}	
}